package id.net.iconpln.bb.server.dao;

import id.net.iconpln.bb.server.util.CommonModule;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class SecmanDao {
	@Autowired 
	private SessionFactory sessionFactory;

	public List<Map> getMasterUnitAll()
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        System.out.println("masuk get secman dao");
        String sql = null;
        CallableStatement call;
        try {
            sql = "{? = call PKG_SECMAN.GetMasterUnitAll}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}

	public List<Map> getMasterLevelUserAll()
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            sql = "{? = call PKG_SECMAN.GetMasterLevelUserAll}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}

	public List<Map> getMasterUserAll()
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            sql = "{? = call PKG_SECMAN.GetMasterUserAll}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}

	public List<Map> getMasterGroupAll()
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            sql = "{? = call PKG_SECMAN.GetMasterGroupAll}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}

	public List<Map> getMasterModulAll()
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            sql = "{? = call PKG_SECMAN.GetMasterModulAll}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}

	public List<Map> getMasterUserGroupAll()
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            sql = "{? = call PKG_SECMAN.GetUserGroupAll}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}

	public List<Map> getMasterGroupAccessAll()
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            sql = "{? = call PKG_SECMAN.GetGroupAccessAll}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}
	
	// Region User
	public List<Map> manageMasterUser(String kode, Map map)
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try 
        {
        	String gethasil = "";
        	
        	if (kode.equalsIgnoreCase("INSERT"))
        	{
	            sql = "{call Pkg_Secman.insertMasterUser(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("idUser").toString());
	            call.setString( 2, map.get("namaLogin").toString());
	            call.setString( 3, map.get("Password").toString());
	            call.setString( 4, map.get("unit").toString());
	            call.setString( 5, map.get("levelUser").toString());
	            call.setString( 6, map.get("namaLengkap").toString());
	            call.setString( 7, map.get("nip").toString());
	            call.setString( 8, map.get("alamat").toString());
	            call.setString( 9, map.get("nomorTelp").toString());
	            call.setString(10, map.get("email").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        	} 
        	else if (kode.equalsIgnoreCase("UPDATE"))
        	{
	            sql = "{call Pkg_Secman.updateMasterUser(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("idUser").toString());
	            call.setString( 2, map.get("namaLogin").toString());
	            call.setString( 3, map.get("Password").toString());
	            call.setString( 4, map.get("PasswordOriginal").toString());
	            call.setString( 5, map.get("unit").toString());
	            call.setString( 6, map.get("levelUser").toString());
	            call.setString( 7, map.get("namaLengkap").toString());
	            call.setString( 8, map.get("nip").toString());
	            call.setString( 9, map.get("alamat").toString());
	            call.setString(10, map.get("nomorTelp").toString());
	            call.setString(11, map.get("email").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        		System.out.println(gethasil);
        	} 
        	else if (kode.equalsIgnoreCase("DELETE"))
        	{
	            sql = "{call Pkg_Secman.deleteMasterUser(?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("idUser").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        	}
        	
            lMap = CommonModule.convertParHasilToResultSet(gethasil);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}
	// End Region User
	
	// Region Group
	public List<Map> manageMasterGroup(String kode, Map map)
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try 
        {
        	String gethasil = "";
        	
        	if (kode.equalsIgnoreCase("INSERT"))
        	{
	            sql = "{call Pkg_Secman.insertMasterGroup(?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("idGroup").toString());
	            call.setString( 2, map.get("namaGroup").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        	} 
        	else if (kode.equalsIgnoreCase("UPDATE"))
        	{
	            sql = "{call Pkg_Secman.updateMasterGroup(?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("idGroup").toString());
	            call.setString( 2, map.get("namaGroup").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        		System.out.println(gethasil);
        	} 
        	else if (kode.equalsIgnoreCase("DELETE"))
        	{
	            sql = "{call Pkg_Secman.deleteMasterGroup(?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("idGroup").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        	}
        	
            lMap = CommonModule.convertParHasilToResultSet(gethasil);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}
	// End Region Group
	
	// Region Modul
	public List<Map> manageMasterModul(String kode, Map map)
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try 
        {
        	String gethasil = "";
        	
        	if (kode.equalsIgnoreCase("INSERT"))
        	{
	            sql = "{call Pkg_Secman.insertMasterModul(?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("idModul").toString());
	            call.setString( 2, map.get("keterangan").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        	} 
        	else if (kode.equalsIgnoreCase("UPDATE"))
        	{
	            sql = "{call Pkg_Secman.updateMasterModul(?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("idModul").toString());
	            call.setString( 2, map.get("keterangan").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        		System.out.println(gethasil);
        	} 
        	else if (kode.equalsIgnoreCase("DELETE"))
        	{
	            sql = "{call Pkg_Secman.deleteMasterModul(?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("idModul").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        	}
        	
            lMap = CommonModule.convertParHasilToResultSet(gethasil);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}
	// End Region Modul
	
	// Region User Group
	public List<Map> manageUserGroup(String kode, Map map)
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try 
        {
        	String gethasil = "";
        	
        	if (kode.equalsIgnoreCase("INSERT"))
        	{
	            sql = "{call Pkg_Secman.insertUserGroup(?, ?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("id_user").toString());
	            call.setString( 2, map.get("id_group").toString());
	            call.setString( 3, map.get("id_modul").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        	} 
        	else if (kode.equalsIgnoreCase("UPDATE"))
        	{
	            sql = "{call Pkg_Secman.updateUserGroup(?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("id_user").toString());
	            call.setString( 2, map.get("id_group").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        		System.out.println(gethasil);
        	} 
        	else if (kode.equalsIgnoreCase("DELETE"))
        	{
	            sql = "{call Pkg_Secman.deleteUserGroup(?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("id_user").toString());
	            call.setString( 2, map.get("id_group").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        	}
        	
            lMap = CommonModule.convertParHasilToResultSet(gethasil);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}
	// End Region User Group
	
	// Region Group Access
	public List<Map> manageGroupAccess(String kode, Map map)
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try 
        {
        	String gethasil = "";
        	
        	if (kode.equalsIgnoreCase("INSERT"))
        	{
	            sql = "{call Pkg_Secman.insertGroupAccess(?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("id_modul").toString());
	            call.setString( 2, map.get("id_group").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        	} 
        	else if (kode.equalsIgnoreCase("UPDATE"))
        	{
	            sql = "{call Pkg_Secman.updateGroupAccess(?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("id_modul").toString());
	            call.setString( 2, map.get("id_group").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        		System.out.println(gethasil);
        	} 
        	else if (kode.equalsIgnoreCase("DELETE"))
        	{
	            sql = "{call Pkg_Secman.deleteGroupAccess(?, ?)}";
	            call = con.prepareCall(sql);
	            call.setString( 1, map.get("id_modul").toString());
	            call.setString( 2, map.get("id_group").toString());
	            call.execute();
	            
	            sql = "{? = call Parhasil.GetHasil}";
	            call = con.prepareCall(sql);
	            call.registerOutParameter(1, OracleTypes.VARCHAR);
	            call.execute();
	            gethasil = (String) call.getObject(1);
        	}
        	
            lMap = CommonModule.convertParHasilToResultSet(gethasil);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}
	// End Region Group Access
	
	// Region User Action
	public List<Map> changePassword(String kode, Map map)
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try 
        {
        	String gethasil = "";
        	
            sql = "{call Pkg_Secman.changePassword(?, ?, ?)}";
            call = con.prepareCall(sql);
            call.setString( 1, map.get("namaLogin").toString());
            call.setString( 2, map.get("PasswordOriginal").toString());
            call.setString( 3, map.get("PasswordNew").toString());
            call.execute();
            
            sql = "{? = call Parhasil.GetHasil}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.execute();
            gethasil = (String) call.getObject(1);
        	
            lMap = CommonModule.convertParHasilToResultSet(gethasil);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}
	// End Region User Action

	public List<Map> getMasterUser(Map map)
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        System.out.println("masuk get secman dao");
        String sql = null;
        CallableStatement call;
        try {
            sql = "{call PARAMETERVIEW.SetUnit(?)}";
            call = con.prepareCall(sql);
            call.setString( 1, map.get("id_unit").toString());
            call.execute();
            
            sql = "{? = call PKG_SECMAN.GetMasterUser}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}

	public List<Map> getMasterUnit(Map map)
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        System.out.println("masuk get secman dao");
        String sql = null;
        CallableStatement call;
        try {
            sql = "{call PARAMETERVIEW.SetUnit(?)}";
            call = con.prepareCall(sql);
            call.setString( 1, map.get("id_unit").toString());
            call.execute();
            
            sql = "{? = call PKG_SECMAN.GetMasterUnit}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}

	public List<Map> getMasterUserAccess(Map map)
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            sql = "{call PARAMETERVIEW.SetUnit(?)}";
            call = con.prepareCall(sql);
            call.setString( 1, map.get("id_unit").toString());
            call.execute();
            
            sql = "{? = call PKG_SECMAN.GetMasterUserAccess}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}


	public List<Map> getMasterModul(Map map)
	{
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            sql = "{call PARAMETERVIEW.SetGroup(?)}";
            call = con.prepareCall(sql);
            call.setString( 1, map.get("id_group").toString());
            call.execute();
            
            sql = "{? = call PKG_SECMAN.GetMasterModul}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        return lMap;
	}
}
